
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_DimCommodity_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_DimCommodity_Update]
GO


create procedure dbo.p_DimCommodity_Update (
	@CommodityKey int, 
	@CommodityIdentifier varchar (20),
	@CommodityName nvarchar (50),
	@BrandName nvarchar (100),
	@SupplierName nvarchar (100),
	@ValueRange varchar (50),
	@Value numeric (18, 3),
	@IsTangible varchar (3),
	@CommodityLine int,
	@ReplacementRate varchar (50),
	@GrossMargin varchar (20),
	@DurationOfSatisfaction varchar (20),
	@DurationOfSearch varchar (20),
	@Model varchar (30),
	@ModelVariant varchar (30),
	@ModelNumber varchar (30),
	@ManufacturerPartNumber varchar (30),
	@SerialNumber varchar (40),
	@BarcodeNumber varchar (40),
	@SizeRange varchar (30),
	@Color varchar (30))
as
begin

	-- Disable row counting
	set nocount on

	declare @Error int,
	        @inTranCount int,
	        @Rows int

	select @Error = 0,
	       @inTranCount = @@trancount

	-- Only start our own transaction if caller has not supplied one
	if (@inTranCount = 0) begin transaction txnDimCommodityUpdate
        else
	    save transaction txnDimCommodityUpdate

	set @Error = @@error
	if (@Error <> 0) goto cleanup

	update dbo.DimCommodity set CommodityIdentifier = @CommodityIdentifier,
				    CommodityName = @CommodityName,
				    BrandName = @BrandName,
				    SupplierName = @SupplierName,
				    ValueRange = @ValueRange,
				    Value = @Value,
				    IsTangible = @IsTangible,
				    CommodityLine = @CommodityLine,
				    ReplacementRate = @ReplacementRate,
				    GrossMargin = @GrossMargin,
				    DurationOfSatisfaction = @DurationOfSatisfaction,
				    DurationOfSearch = @DurationOfSearch,
				    Model = @Model,
				    ModelVariant = @ModelVariant,
				    ModelNumber = @ModelNumber,
				    ManufacturerPartNumber = @ManufacturerPartNumber,
				    SerialNumber = @SerialNumber,
				    BarcodeNumber = @BarcodeNumber,
				    SizeRange = @SizeRange,
				    Color = @Color
	  where (CommodityKey = @CommodityKey)

	select @Error = @@error,
	       @Rows = @@rowcount
	if ((@Error = 0) and
	    (@Rows = 0)) set @Error = -2
	if (@Error <> 0) goto cleanup

cleanup:

	-- End transaction
	if (@@trancount > 0)
	begin

		-- On error roll back to save point or local transaction else commit
		if (@Error <> 0) rollback transaction txnDimCommodityUpdate
		else
		if (@inTranCount = 0) commit transaction txnDimCommodityUpdate

	end

	return @Error

end
GO

grant execute on dbo.p_DimCommodity_Update  to [db_datawriter]
go
